"""
ComplianceItem CRUD 操作
定义所有与数据库交互的函数，隔离业务代码与SQL语句
"""
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, func      
from sqlalchemy.orm import selectinload
from typing import Optional, List, Dict, Any, Tuple
from uuid import UUID
from datetime import datetime

from models.complianceItem import ComplianceItem
from schemas.complianceItem import ComplianceItemCreate, ComplianceItemUpdate
from log import log_info, log_error

async def get_compliance_items(
    db: AsyncSession, 
    skip: int = 0, 
    limit: int = 100,
    filters: Optional[Dict[str, Any]] = None
) -> Tuple[List[ComplianceItem], int]:
    """
    获取合规项列表，支持分页和过滤

    Args:
        db: 数据库会话
        skip: 跳过记录数
        limit: 返回记录限制
        filters: 过滤条件字典

    Returns:
        包含合规项列表和总记录数的元组
    """
    query = select(ComplianceItem)
    count_query_base = select(func.count(ComplianceItem.id)) # Base for count query
    
    # 应用过滤条件
    if filters:
        if filters.get("assessment_plan_id"):
            condition = ComplianceItem.assessment_plan_id == filters["assessment_plan_id"]
            query = query.filter(condition)
            count_query_base = count_query_base.filter(condition)
        if filters.get("title"):
            condition = ComplianceItem.title.ilike(f"%{filters['title']}%")
            query = query.filter(condition)
            count_query_base = count_query_base.filter(condition)
        if filters.get("compliance_type"):
            condition = ComplianceItem.compliance_type == filters["compliance_type"]
            query = query.filter(condition)
            count_query_base = count_query_base.filter(condition)
        if filters.get("risk_level"):
            condition = ComplianceItem.risk_level == filters["risk_level"]
            query = query.filter(condition)
            count_query_base = count_query_base.filter(condition)
        if filters.get("compliance_status"):
            condition = ComplianceItem.compliance_status == filters["compliance_status"]
            query = query.filter(condition)
            count_query_base = count_query_base.filter(condition)
        if filters.get("progress_status"):
            condition = ComplianceItem.progress_status == filters["progress_status"]
            query = query.filter(condition)
            count_query_base = count_query_base.filter(condition)
        if filters.get("inspector"):
            condition = ComplianceItem.inspector.ilike(f"%{filters['inspector']}%")
            query = query.filter(condition)
            count_query_base = count_query_base.filter(condition)
    
    # 计算总记录数
    total_result = await db.execute(count_query_base)
    total = total_result.scalar_one() or 0
    
    # 应用分页获取项目
    items_result = await db.execute(query.order_by(ComplianceItem.updated_at.desc()).offset(skip).limit(limit))
    items = items_result.scalars().all()
    
    log_info(f"Retrieved {len(items)} compliance items out of {total}", module="crud.complianceItem")
    return items, total

async def get_compliance_item_by_id(db: AsyncSession, item_id: UUID) -> Optional[ComplianceItem]:
    """
    根据ID获取合规项

    Args:
        db: 数据库会话
        item_id: 合规项ID

    Returns:
        合规项对象，如未找到则返回None
    """
    query = select(ComplianceItem).where(ComplianceItem.id == item_id).options(selectinload(ComplianceItem.assessment_plan))
    result = await db.execute(query)
    item = result.scalar_one_or_none()
    
    if item:
        log_info(f"Retrieved compliance item with ID {item_id}", module="crud.complianceItem")
    else:
        log_info(f"No compliance item found with ID {item_id}", module="crud.complianceItem")
    
    return item

async def get_compliance_items_by_plan_id(
    db: AsyncSession, 
    plan_id: UUID,
    skip: int = 0,
    limit: int = 100
) -> Tuple[List[ComplianceItem], int]:
    """
    根据评估计划ID获取所有关联的合规项

    Args:
        db: 数据库会话
        plan_id: 评估计划ID
        skip: 跳过记录数
        limit: 返回记录限制

    Returns:
        包含合规项列表和总记录数的元组
    """
    # 构建主查询和计数查询的条件
    condition = ComplianceItem.assessment_plan_id == plan_id

    # 计算总记录数
    count_query = select(func.count(ComplianceItem.id)).filter(condition)
    total_result = await db.execute(count_query)
    total = total_result.scalar_one() or 0
    
    # 获取分页后的项目
    items_query = select(ComplianceItem).filter(condition).order_by(ComplianceItem.updated_at.desc()).offset(skip).limit(limit)
    items_result = await db.execute(items_query)
    items = items_result.scalars().all()
    
    log_info(f"Retrieved {len(items)} compliance items for plan ID {plan_id} out of {total}", module="crud.complianceItem")
    return items, total

async def create_compliance_item(db: AsyncSession, item: ComplianceItemCreate) -> ComplianceItem:
    """
    创建新的合规项

    Args:
        db: 数据库会话
        item: 合规项创建模型

    Returns:
        创建的合规项对象
    """
    # 从Pydantic模型创建ORM实例
    db_item = ComplianceItem(
        assessment_plan_id=item.assessment_plan_id,
        title=item.title,
        description=item.description,
        compliance_type=item.compliance_type,
        risk_level=item.risk_level,
        compliance_status=item.compliance_status,
        progress_status=item.progress_status,
        inspector=item.inspector,
        updated_at=datetime.now()
    )
    
    # 添加到数据库
    db.add(db_item)
    await db.commit()
    await db.refresh(db_item)   
    
    log_info(f"Created new compliance item with ID {db_item.id}", module="crud.complianceItem")
    return db_item

async def bulk_create_compliance_items(db: AsyncSession, items: List[ComplianceItemCreate]) -> List[ComplianceItem]:
    """
    批量创建合规项

    Args:
        db: 数据库会话
        items: 合规项创建模型列表

    Returns:
        创建的合规项对象列表
    """
    db_items = [ComplianceItem(**item.model_dump()) for item in items]
    
    try:
        # 批量添加到数据库
        for item in db_items:
            db.add(item)
        
        await db.commit()
        
        # 刷新每个项 (只有在commit成功后才执行)
        for item in db_items:
            await db.refresh(item)
            
        log_info(f"Bulk created {len(db_items)} compliance items", module="crud.complianceItem")
        return db_items
    except Exception as e: # Catch any exception during add, commit, or refresh
        await db.rollback()
        log_error(f"Error during bulk_create_compliance_items, transaction rolled back: {str(e)}", module="crud.complianceItem")
        raise # Re-raise the exception to be handled by higher-level error handlers

async def update_compliance_item(
    db: AsyncSession, 
    item_id: UUID, 
    item_update: ComplianceItemUpdate
) -> Optional[ComplianceItem]:
    """
    更新现有合规项

    Args:
        db: 数据库会话
        item_id: 要更新的合规项ID
        item_update: 包含更新数据的模型

    Returns:
        更新后的合规项对象，如未找到则返回None
    """
    # 获取现有合规项
    db_item = await get_compliance_item_by_id(db, item_id)
    if db_item is None:
        log_info(f"Update failed: No compliance item found with ID {item_id}", module="crud.complianceItem")
        return None
    
    # 更新非空字段
    update_data = item_update.model_dump(exclude_unset=True)
    
    # 特殊处理: 当compliance_type更新为guarantee时，清空risk_level
    if update_data.get("compliance_type") == "guarantee":
        update_data["risk_level"] = None
    
    # 特殊处理: 当compliance_type更新为risk时，确保risk_level有值
    if update_data.get("compliance_type") == "risk" and "risk_level" not in update_data:
        if db_item.risk_level is None:
            log_error(f"Update failed: Compliance type is risk but no risk level specified for item ID {item_id}", 
                     module="crud.complianceItem")
            raise ValueError(f"当合规类型更新为 'risk' 时，必须提供 'risk_level'，因为当前项目 (ID: {item_id}) 的风险级别未设置。")
    
    for key, value in update_data.items():
        setattr(db_item, key, value)
    
    await db.commit()   
    await db.refresh(db_item)   
    
    log_info(f"Updated compliance item with ID {item_id}", module="crud.complianceItem")
    return db_item

async def delete_compliance_item(db: AsyncSession, item_id: UUID) -> bool:
    """
    删除合规项

    Args:
        db: 数据库会话
        item_id: 要删除的合规项ID

    Returns:
        删除成功返回True，如未找到则返回False
    """
    db_item = await get_compliance_item_by_id(db, item_id)
    if db_item is None:
        log_info(f"Delete failed: No compliance item found with ID {item_id}", module="crud.complianceItem")
        return False
    
    await db.delete(db_item)
    await db.commit()
    
    log_info(f"Deleted compliance item with ID {item_id}", module="crud.complianceItem")
    return True
